from osv import fields,osv
import mx.DateTime
import tools.sql
from tools.translate import _

class kdvn_project_workinprogresslist_report(osv.osv):
    _name='kdvn.project.workinprogresslist.report'
    _description='KDVN Project Work in Progress List'
    _auto=False
    _columns={
            'project_id':fields.many2one('kdvn.project','Project'),
            'total_budget':fields.float('Total Budget'),
            'project_amount_vnd':fields.float('Project Amount'),
            'claim_amount':fields.float('Claim Amount'),
            'recv_amount':fields.float('Recv Amount'),
        
            'material_budget_amount':fields.float('Material Budget Amount'),
            'site_expense_budget_amount':fields.float('Site Expense Budget Amount'),
            'sub_contractor_budget_amount':fields.float('Sub Contractor Budget Amount'),
            'gse_budget_amount':fields.float('GSE Budget Amount'),
            'kfp_budget_amount':fields.float('KFP Budget Amount'),
            'profit_budget_amount':fields.float('Profit Budget Amount'),

            'material_order_vnd':fields.float('Material Order'),
            'site_expense_order_vnd':fields.float('Site Expense Order'),
            'sub_contractor_order_vnd':fields.float('Sub Contractor Order'),
            'gse_order_vnd':fields.float('GSE Order'),
            'kfp_order_vnd':fields.float('KFP Order'),
            'profit_order_vnd':fields.float('Profit Order'),
            'material_payment_vnd':fields.float('Material Payment'),
            'site_expense_payment_vnd':fields.float('Site Expense Payment'),
            'sub_contractor_payment_vnd':fields.float('Sub Contractor Payment'),
            'gse_payment_vnd':fields.float('GSE Payment'),
            'kfp_payment_vnd':fields.float('KFP Payment'),
            'profit_payment_vnd':fields.float('Profit Payment'),
            'project_received_vat':fields.float('Profit Payment'),
            'prj_issued_vat':fields.float('Profit Payment')
              }
#    def init(self,cr):
#        tools.sql.drop_view_if_exists(cr,'kdvn_project_workinprogresslist_report')
#        cr.execute("Create or replace view kdvn_project_workinprogresslist_report as\
#                        Select\
#                        kp.id,\
#                        kp.id as project_id,\
#                        total_budget,\
#                        coalesce((case when coalesce(pc_vnd.rate,0)=0 then 0 else project_amount*pc.rate/pc_vnd.rate end),0) as project_amount_vnd,\
#                        vwcr.prj_claim_amount_vnd as claim_amount,\
#                        vwcr.prj_recv_amount_vnd as recv_amount,\
#                        \
#                        sum(case when category='material' then coalesce(budget_amount,0) else 0 end) as material_budget_amount,\
#                        sum(case when category='site_expense' then coalesce(budget_amount,0) else 0 end) as site_expense_budget_amount,\
#                        sum(case when category='sub_contractor' then coalesce(budget_amount,0) else 0 end) as sub_contractor_budget_amount,\
#                        sum(case when category='gse' then coalesce(budget_amount,0) else 0 end) as gse_budget_amount,\
#                        sum(case when category='kfp' then coalesce(budget_amount,0) else 0 end) as kfp_budget_amount,\
#                        sum(case when category='profit' then coalesce(budget_amount,0) else 0 end) as profit_budget_amount,\
#                        \
#                        sum(case when category='material' then coalesce(subtotalvnd,0) else 0 end) as material_order_vnd,\
#                        sum(case when category='site_expense' then coalesce(subtotalvnd,0) else 0 end) as site_expense_order_vnd,\
#                        sum(case when category='sub_contractor' then coalesce(subtotalvnd,0) else 0 end) as sub_contractor_order_vnd,\
#                        sum(case when category='gse' then coalesce(subtotalvnd,0) else 0 end) as gse_order_vnd,\
#                        sum(case when category='kfp' then coalesce(subtotalvnd,0) else 0 end) as kfp_order_vnd,\
#                        sum(case when category='profit' then coalesce(subtotalvnd,0) else 0 end) as profit_order_vnd,\
#                        \
#                        sum(case when category='material' then coalesce(payment_vnd,0) else 0 end) as material_payment_vnd,\
#                        sum(case when category='site_expense' then coalesce(payment_vnd,0) else 0 end) as site_expense_payment_vnd,\
#                        sum(case when category='sub_contractor' then coalesce(payment_vnd,0) else 0 end) as sub_contractor_payment_vnd,\
#                        sum(case when category='gse' then coalesce(payment_vnd,0) else 0 end) as gse_payment_vnd,\
#                        sum(case when category='kfp' then coalesce(payment_vnd,0) else 0 end) as kfp_payment_vnd,\
#                        sum(case when category='profit' then coalesce(payment_vnd,0) else 0 end) as profit_payment_vnd\
#                        \
#                        from \
#                        kdvn_project kp\
#                        left join\
#                        vwproject_claim_received vwcr on kp.id = vwcr.id\
#                        left join\
#                        project_currency pc on kp.id = pc.project_id and default_curr=True\
#                        left join\
#                        project_currency pc_vnd on kp.id = pc_vnd.project_id and pc_vnd.name='VND'\
#                        left join\
#                        kdvn_budget_data kbd on kp.id=kbd.project_id\
#                        left join\
#                        (select\
#                        project_id,\
#                        sum(budget_amount) as total_budget\
#                        from \
#                        kdvn_budget_data group by project_id) vwtemp on kbd.project_id = vwtemp.project_id\
#                        left join\
#                        vwbudget_category vwbc on kbd.budget_id=vwbc.budget_id\
#                        left join\
#                        (Select \
#                        pol.project_id,\
#                        kdvn_budget_id as budget_id,\
#                        coalesce(sum(price_subtotal2*rate*fnpo_compute(fnpo_currnametoid('VND'),po.currency_id,date_order)),0) as subtotalvnd,\
#                        sum(payment_vnd) as payment_vnd\
#                        from \
#                        purchase_order po\
#                        left join \
#                        purchase_order_line pol on po.id=order_id\
#                        left join \
#                        vwpo_rate vwr on po.id=vwr.id\
#                        left join \
#                        product_product pp on product_id=pp.id\
#                        left join\
#                        (Select\
#                        kropd.project_id,\
#                        budget_id,\
#                        sum(fnpo_compute(currency_id,fnpo_currnametoid('VND'),rop_date)*(coalesce(kropd.amount,0) + coalesce(kropd.retention_amount,0) + coalesce(kropd.advanced_amount,0))) as payment_vnd\
#                        from\
#                        kdvn_request_of_payment krop\
#                        left join\
#                        kdvn_request_of_payment_detail kropd on krop.id = kropd.rop_id\
#                        inner join\
#                        (Select distinct payment_id from kdvn_payment) kp on krop.id=kp.payment_id\
#                        where\
#                        krop.state not in ('draft','cancel') and coalesce(kp.payment_id,0)>0 and coalesce(order_id,0)>0\
#                        Group by \
#                        kropd.project_id,budget_id) vwtemp2 on pol.project_id=vwtemp2.project_id and kdvn_budget_id=vwtemp2.budget_id\
#                        where \
#                        po.state not in ('draft','cancel')\
#                        group by \
#                        pol.project_id,kdvn_budget_id) vwtemp1 on kbd.budget_id=vwtemp1.budget_id and kbd.project_id=vwtemp1.project_id\
#                        where\
#                        category is not null\
#                        group by\
#                        kp.id,total_budget,pc_vnd.rate,pc.rate,project_amount,prj_claim_amount_vnd,vwcr.prj_recv_amount_vnd")
kdvn_project_workinprogresslist_report()

class kdvn_project(osv.osv):
    _name = 'kdvn.project'
    _inherit = 'kdvn.project'
    _description="KDVN Project"
    
    _columns={
              'project_workinprogresslist_ids':fields.one2many('kdvn.project.workinprogresslist.report','id','Project Work In Progress List')
              } 
kdvn_project()